/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.edu.pucp.TrabajoGrupal.Classes;
import java.math.BigInteger;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.util.logging.*;
import javax.swing.JFrame;
import javax.swing.JTable;
import pe.edu.pucp.TrabajoGrupal.Others.DbUtils;

/**
 *
 * @author ronald
 */
public class ClassDAO {
    private Connection con = null;
    private Statement st = null;
    private Statement st1 = null;
    private ResultSet rs = null;
    
    public ClassDAO(){
       
    }

    
    private void conectar(){
         try
        {
            String url = "jdbc:mysql://localhost:3306/dbproyectos";
            String usr = "root";
            String password = "";
            //Class.forName("com.mysql.jdbc.Driver");
            //DriverManager.registerDriver(new org.gjt.mm.mysql.Driver());
            con = DriverManager.getConnection (url,usr, password);            
        } catch (SQLException ex) {

        }
    }
    private void cerrar(){
        try{
            st.close();
            con.close();
        }
        catch(Exception e){
        
        }
    }
    public static String getMD5(String input) {
        try {
        MessageDigest md = MessageDigest.getInstance("MD5");
        byte[] messageDigest = md.digest(input.getBytes());
        BigInteger number = new BigInteger(1, messageDigest);
        String hashtext = number.toString(16);
        while (hashtext.length() < 32) {
            hashtext = "0" + hashtext;
        }
        return hashtext;
        }
        catch (NoSuchAlgorithmException e) {
            throw new RuntimeException(e);
        }
    }
    public void guardar(Project project){
        conectar();
        try{
            st = con.createStatement();
            //String cadena = "INSERT INTO proyecto (`proyecto`, `horastotal`, `inicio`, `fin`) VALUES ('"+project.getName() +"','"+ project.getTotalHours() +"','"+ project.getStartDate() +"', '"+ project.getFinishDate() +"')";
            String cadena = "INSERT INTO proyecto (`proyecto`, `inicio`, `fin`,`horasTotales`) VALUES " +
                    "('"+project.getName()  +"',(SELECT STR_TO_DATE('"+ project.getStartDate() + 
                    "','%d/%m/%Y')), (SELECT STR_TO_DATE('"+ project.getFinishDate() +"','%d/%m/%Y')),'"+project.getTotalHours()+"')";
            st.execute(cadena);
            //cadena = "select "
            /*rs = st.executeQuery("select * from proyecto");
            if ( rs.next()){saveButtonMouseClicked
                System.out.println(rs.getString(0));            
            }*/
        }
        catch(Exception e){
            
        }
        cerrar();
    }
    public void guardar(User project){
        conectar();
        try{
            int num=2;
            st = con.createStatement();
            //String cadena = "INSERT INTO proyecto (`proyecto`, `horastotal`, `inicio`, `fin`) VALUES ('"+project.getName() +"','"+ project.getTotalHours() +"','"+ project.getStartDate() +"', '"+ project.getFinishDate() +"')";
            if ("ADMIN".equals(project.getRole()))
                num =1;
            String query = "INSERT IGNORE usuario (`usuario`, `clave`, `nombre`, `email`, `estado`, `idRol`) VALUES ('"+project.getUser()  +"',(SELECT MD5('"+ project.getPassword() +"')), '"+ project.getName() +"', '"+ project.getEmail() +"', '"+ "1" +"', '"+ num +"')";
            st.execute(query);
            /*rs = st.executeQuery("select * from proyecto");
            if ( rs.next()){saveButtonMouseClicked
                System.out.println(rs.getString(0));            
            }*/
            User user=null;
            Session ses = Session.getInstance();
            try{
                user = ses.getUserData();        
            }
            catch(Exception e){           
            }
            String query2 = "INSERT into usuario_proyecto values ( , )";
            query2+=user.getUserId();
            /*String query3 = "SELECT tareaid FROM tarea WHERE tareanombre = '"+ Task+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query3);
            while (rs.next()){
                Task = rs.getString("tareaid");
            }
            query2+= "";*/
        }
        catch(Exception e){
            
        }
        cerrar();
    }
    public  User Authentication(String user, String password){
        User us = null;
        conectar();        
        try{
            String query = "SELECT u.*, r.* FROM usuario u, roles r WHERE u.usuario = '"+user+"' and u.clave = (select md5('"+password+"')) and r.idRol = u.idRol ";
            st = con.createStatement();
            rs = st.executeQuery(query);
            while (rs.next()){
                us = new User();
                us.setUserId(Integer.parseInt(rs.getString("usuarioid")));
                us.setUser(rs.getString("usuario"));
                us.setPassword(rs.getString("clave"));
                us.setName(rs.getString("nombre"));
                us.setEmail(rs.getString("email"));
                us.setRole(rs.getString("nombreRol"));
                if (rs.getString("estado").compareTo("1") == 0) us.setState("Habilitado");
                else us.setState("Inhabilitado");
            }
        }
        catch(Exception e){
        }
        cerrar();
        return us;
    }
    public void Update(User user){        
        conectar();
        try{
            //String query = "SELECT u.*, r.* FROM usuario u, roles r WHERE u.idusuario = "+user.getUserId();
            String query = "UPDATE usuario SET usuario ='"+ user.getUser() + "' , clave = '"+user.getPassword()+"' , nombre = '"+user.getName()+"' , email = '"+user.getEmail()+"'  WHERE idusuario ="+user.getUserId();
            st = con.createStatement();
            rs = st.executeQuery(query);            
        }
        catch(Exception e){
        }
        cerrar();        
    
    }
    public void UpdatePassword(User user){        
        conectar();
        try{
            //String query = "SELECT u.*, r.* FROM usuario u, roles r WHERE u.idusuario = "+user.getUserId();
            String query = "UPDATE usuario SET clave = (select md5('"+user.getPassword()+"')) WHERE usuarioid ="+user.getUserId();
            st = con.createStatement();
            st.execute(query);            
        }
        catch(Exception e){
        }
        cerrar();        
    
    }
    public void InsertTask(String Project, String Task, String hours, String Details){
        
        conectar();
        try{
            String query = "INSERT INTO tarea (`proyectoid`, `tareanombre`, `horastrabajo`,`descripcion`) VALUES" +
                   "((SELECT proyectoid FROM proyecto WHERE proyecto = '"+ Project+ "'), '"+ Task +"' , '"+hours + "', '"+ Details+ "');";
            st = con.createStatement();
            st.execute(query);            
        }
        catch(Exception e){
        }
        
        cerrar();
    }    
    public void InsertAdvance(String Project, String Task, String UserId, String hours, String Details){
        conectar();
        try{
            /*String query1 = "SELECT proyectoid FROM proyecto WHERE proyecto = '"+ Project+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query1);
            while (rs.next()){
                Project = rs.getString("proyectoid");
            } */     
            String query2 = "SELECT tareaid FROM tarea WHERE tareanombre = '"+ Task+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query2);
            while (rs.next()){
                Task = rs.getString("tareaid");
            }
            String query = "INSERT INTO avance (`tareaid`, `usuarioid`, `horas`, `observaciones`) VALUES" +
                   "(" + Task + ", '"+ UserId +"' , '"+hours + "', '"+ Details+ "')";
            st = con.createStatement();
            st.execute(query);            
        }
        catch(Exception e){
        }
        
        cerrar();
    }
    /*public  void UpdateTable(JTable table){
        conectar();
        try{
            String query = "select u.usuarioid, u.usuario, u.nombre, u.email, r.nombreRol from usuario u , roles r where u.idRol = r.idRol";
            st = con.createStatement();
            rs = st.executeQuery(query);
            table.setModel(DbUtils.resultSetToTableModel(rs));
        }
        catch(Exception e){
        }
        cerrar();
    }
    public  void UpdateTable(JTable table){
        conectar();
        try{
            String query = "select u.usuarioid, u.usuario, u.nombre, u.email, r.nombreRol from usuario u , roles r where u.idRol = r.idRol";
            st = con.createStatement();
            rs = st.executeQuery(query);
            table.setModel(DbUtils.resultSetToTableModel(rs));
        }
        catch(Exception e){
        }
        cerrar();
    }*/
    public  void UpdateTable(JTable table, String query){
        conectar();
        try{
            //String query = "select t.tareaid, t.tareanombre, p.proyecto, t.descripcion, t.horastrabajo from tarea t, proyecto p where t.proyectoid = p.proyectoid";
            st = con.createStatement();
            rs = st.executeQuery(query);
            table.setModel(DbUtils.resultSetToTableModel(rs));
        }
        catch(Exception e){
        }
        cerrar();
    }
    /*public  void UpdateTable3(JTable table){
        conectar();
        User user=null;
        Session ses = Session.getInstance();
        try{
        user = ses.getUserData();        
        }
        catch(Exception e){           
        }
        try{
            //String query = "select * from proyecto where proyectoid in (select proyectoid from usuario_proyecto where usuarioid = '" + user.getUserId()+ "')";
            int idusuario = user.getUserId();
            //String query = "select * from proyecto where proyectoid in (select proyectoid from usuario_proyecto where usuarioid = 32)";
            String query = "select * from proyecto where proyectoid in (select proyectoid from usuario_proyecto where usuarioid =" + idusuario + ")";
            
            st = con.createStatement();
            rs = st.executeQuery(query);
            table.setModel(DbUtils.resultSetToTableModel(rs));
        }
        catch(Exception e){
        }
        cerrar();
    }*/
    public void updateUser(User user){
        conectar();
        try{
            int num=2;
            st = con.createStatement();
            //String cadena = "INSERT INTO proyecto (`proyecto`, `horastotal`, `inicio`, `fin`) VALUES ('"+project.getName() +"','"+ project.getTotalHours() +"','"+ project.getStartDate() +"', '"+ project.getFinishDate() +"')";
            if ("ADMIN".equals(user.getRole()))
                num =1;
            String query = "UPDATE usuario SET usuario = '"+user.getUser()+"', nombre = '"+user.getName()+"', email = '"+user.getEmail()+"', idRol = '"+num+"' where usuarioid = '"+user.getUserId()+"'";
            st.execute(query);
            /*rs = st.executeQuery("select * from proyecto");
            if ( rs.next()){saveButtonMouseClicked
                System.out.println(rs.getString(0));            
            }*/
        }
        catch(Exception e){
            
        }
        cerrar();
    }
    public void linkProjects(String user, String Project){
        conectar();
        try{            
            String query2 = "SELECT usuarioid FROM usuario WHERE usuario = '"+ user+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query2);
            while (rs.next()){
                user = rs.getString("usuarioid");
            }
            String query3 = "SELECT proyectoid FROM proyecto WHERE proyecto = '"+ Project+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query3);
            while (rs.next()){
                Project = rs.getString("proyectoid");
            }
            String query = "INSERT INTO usuario_proyecto (`proyectoid`, `usuarioid`) VALUES" +
                   "(" + Project + ", '"+ user +"')";
            st = con.createStatement();
            st.execute(query);
        }
        catch(Exception e){
            
        }
        cerrar();
    }
    public void GenerateAdvance(String Project, String user){
        conectar();
        try{            
            String query2 = "SELECT usuarioid FROM usuario WHERE usuario = '"+ user+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query2);
            while (rs.next()){
                user = rs.getString("usuarioid");
            }
            //Imprimir: Projecto = Project
            String query3 = "SELECT proyectoid FROM proyecto WHERE proyecto = '"+ Project+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query3);
            while (rs.next()){
                Project = rs.getString("proyectoid");
            }
            String query4 = "SELECT tareaid, tareanombre, horastrabajo FROM tarea WHERE proyectoid = '"+ Project+ "'";
            st = con.createStatement();
            rs = st.executeQuery(query3);
            int totaladvance=0;
            while (rs.next()){
                int partialadvance=0;
                int partialpercent=0;
                String taskName = rs.getString("tareanombre");
                int taskTime = rs.getInt("horastrabajo");
                //Imprimir: Tarea = taskname        Horas Estimadas = tasktime
                String taskId = rs.getString("tareaid");
                String query5 ="SELECT observaciones, horas FROM avance WHERE tareaid = '"+ taskId+ "'" ;                
                //Imprimir: Observacion     Horas Trabajadas        Porcentaje de Horas
                st = con.createStatement();
                rs = st.executeQuery(query4);
                
                while (rs.next()){
                    String description = rs.getString("observaciones");
                    int time = rs.getInt("horas");
                    int percent = time*100/taskTime;
                    //Imprimir: descripcion     time        percent "%"
                    partialpercent+=percent;                    
                    partialadvance+=time;
                }
                //Imprimir: Total avanzado      partialadvance      partialpercent "%"
                totaladvance+=partialadvance;
            }                        
            //Imprimir: Tiempo Invertido  totaladvance "Horas"
        }
        catch(Exception e){
            
        }
        cerrar();
    }
}
